<?php
header ( "Content-type:text/html;charset=utf-8" );
// 关闭notice错误
/* error_reporting ( 0 );*/
define ( 'D_BUG', '1' ); 
$_SGLOBAL = array ();
$_SC = array ();
$_SC ['dbhost'] = '127.0.0.1'; // 服务器地址
$_SC ['dbuser'] = 'root'; // 用户
$_SC ['dbpw'] = 'root'; // 密码
$_SC ['dbcharset'] = 'utf8'; // 字符集
$_SC ['pconnect'] = 0; // 是否持续连接
$_SC ['dbname'] = 'iEnjoy'; // 数据库
$_SC ['charset'] = 'utf-8'; // 页面字符集


// 时间
$mtime = explode ( ' ', microtime () );
$_SGLOBAL ['timestamp'] = $mtime [1];
$_SGLOBAL ['supe_starttime'] = $_SGLOBAL ['timestamp'] + $mtime [0];
// 链接数据库
dbconnect ();

	
	$radionum = $reqnum = $trwupnum = $pbnum = $qbupdatenum = 0;
	$radionum1 = $reqnum1 = $trwupnum1 = $pbnum1 = $qbupdatenum1 = 0;
	
	echo "蜻蜓数据 电台之家数据开始比对...................................";
	// 蜻蜓数据中，tbl_radio表中 已存在的，以电台之家数据为准，修改
	$urqt = getRadioQt ();
	
	while ( $uvalue = $_SGLOBAL ['db']->fetch_array ( $urqt ) ) { 
		$radiosarr=null;
		if ($uvalue ['channelid']) {
			$rp = getRadioProgram ( $uvalue ['channelid'] );
			$program = $_SGLOBAL ['db']->fetch_array ( $rp );
		}
		if ($uvalue ['type'] && !$uvalue ['channel']) {
			$radiosarr ['channel'] = $uvalue ['type'];
		}
		
		if(!$uvalue['rdescription']){
			$radiosarr ['description'] = addslashes ( $uvalue ['description']);
		}
		
		if (isset($program ['playurl']) ) {
			if($uvalue ['play_url']!=$program ['playurl']){
				$radiosarr ['play_url'] = $program ['playurl'];
			}
		}
		if(is_array($radiosarr)){
			$radiosarr['check_state'] = 1; 
			$radiosarr["source"] = 2;
			$radiosarr["update_time"] = time ();
			$wherearr = array ("id" => $uvalue ['id'] );
			
			
			if(updatetable ( "tbl_radio", $radiosarr, $wherearr, 1 )) {
				$radioid = $uvalue ['id'];
		
				$radionum ++;
			}
			
		}
		
		if (isset($radioid)) {
			
			
			$radionum1 ++;
			
			
			
			$rfq = getRadioFreq($radioid);
			$radiofreq = $_SGLOBAL ['db']->fetch_array ( $rfq );
			
			
			if($radiofreq){
		
				if ($uvalue ['rid'] == 96 && isset($radioFreqsarr ["country"])) {
					if($radioFreqsarr ["country"]!='GLOBAL'){
						$radioFreqsarr ["country"] = 'GLOBAL';
					}
				}
				
				if(!$radiofreq['frequency'] && $uvalue ['frequency']){
					$radioFreqsarr["frequency"] = $uvalue ['frequency'] * 1000;
				}
				
				
				if(isset($radioFreqsarr)){
			
					$reqnum ++;
					$radioFreqsarr["update_time"] = time ();
					$wherearr = array ("radio_id" => $radioid );
						
					if (updatetable ( "tbl_radio_freq", $radioFreqsarr,$wherearr )) {
						$reqnum1 ++;
					}
				}
					
			
			}else{
				
				$radioFreqsarr = array ("radio_id" => $radioid, "frequency" => $uvalue ['frequency'] * 1000,  "update_time" => time () );
				if ($uvalue ['rid'] == 96) {
					$radioFreqsarr ["country"] = 'GLOBAL';
				}
					
				$trwupnum ++;
					
				$rfqid = inserttable ( "tbl_radio_freq", $radioFreqsarr,1 );
				if ($rfqid) {
					$trwupnum1 ++;
				}
			}
		
			
			
		
			while ( $uvalue1 = $_SGLOBAL ['db']->fetch_array ( $rp ) ) {
				
			/* 	dump($uvalue1);
				echo "<br>";
				echo '_____________________________________________________';
				echo "<br>"; */
				$pbl = getPlayBill($radioid,$uvalue1['broadcasttime']);
				$uvalue2 = $_SGLOBAL ['db']->fetch_array ( $pbl );
				
			/* 	 	dump($uvalue2);
					echo "<br>";
				echo '*******************************************************';
				echo "<br>";  */
					if($uvalue2){
					

						if(!$uvalue2['title'] && $uvalue1['title']){
							$playBillarr['title'] = $uvalue1['title'];
						}
						
						if(!$uvalue2['broadcaster'] && $uvalue1['broadcaster']){
							$playBillarr['broadcaster'] = $uvalue1['broadcaster'];
						}
						
						if(isset($playBillarr)){
							$playBillarr['update_time'] = time();
							if(updatetable('tbl_playbill', $playBillarr, array('id'=>$uvalue2['id']))){
								$qbupdatenum ++;
							}
							$qbupdatenum1 ++;
						}
					}else{
						$playBillarr = array ("radio_id" => $radioid, "title" => $uvalue1 ['title'], "start_time" => $uvalue1 ['broadcasttime'], "broadcaster" => $uvalue1 ['broadcaster'], "create_time" => time (), "update_time" => time () );
						$pbid = inserttable ( "tbl_playbill", $playBillarr, 1 );
						if ($pbid) {
							$pbnum1 ++;
						}
						$pbnum ++;
					}
					
				
			}
		
		}
	
	}
	echo "<br>";
	echo "<br>";
	echo "蜻蜓数据对比完成！ ";
	
	echo "<br>";
	echo "<br>";
	
	echo "tbl_radio表应修改:$radionum.实修改:$radionum1";
	echo "<br>";
	echo "tbl_radio_freq表应修改:$reqnum.实修改:$reqnum1";
	echo "<br>";
	
	echo "tbl_radio_freq表应插入:$trwupnum.实插入:$trwupnum1";
	echo "<br>";
	
	echo "tbl_playbill表应插入:$pbnum.实插入:$pbnum1";
	echo "<br>";
	echo "<br>";
	echo "tbl_playbill表应修改:$qbupdatenum.实修改:$qbupdatenum1";
	echo "<br>";
	echo "<br>";
	echo "<br>";
	echo "<br>";
	echo "<br>";




$radionum = $reqnum = $pbnum = 0;
$radionum1 = $reqnum1 = $pbnum1 = 0;

	echo "蜻蜓新数据开始补全...................................";
	
	echo "<br>";
	echo "<br>";
	// 蜻蜓数据中，tbl_radio表中 不存在的，插入
	$rqt = getRadioQt ( 1 );
	
	
	
	while ( $value = $_SGLOBAL ['db']->fetch_array ( $rqt ) ) {
		
		
		if ($value ['channelid']) {
			$rp = getRadioProgram ( $value ['channelid'] );
			$program = $_SGLOBAL ['db']->fetch_array ( $rp );
		}
		
		$radiosarr = array ("name" => $value ['name'], "channel" => $value ['type'], "description" => addslashes ( $value ['description'] ), "play_url" => $program ['playurl'], 'check_state' => 1, "source" => 2, "create_time" => time (), "update_time" => time () );
		
		$radioid = inserttable ( "tbl_radio", $radiosarr, 1 );
		$reqnum ++;
		if ($radioid) {
			$radionum ++;
		$radionum1 ++;
			
			$radioFreqsarr = array ("radio_id" => $radioid,"create_time" => time (), "update_time" => time () );
			
			if(522<$value['frequency']<1710){
				$radioFreqsarr['freq_type']='AM';
				$radioFreqsarr['frequency']=$value['frequency'];
			}elseif(87.5<$radioFreqsarr['frequency']<108){
				$radioFreqsarr['freq_type']='FM';
				$radioFreqsarr['frequency']=$value['frequency']*1000;
			}
				
			
			if ($value ['rid'] == 96) {
				$radioFreqsarr ["country"] = 'GLOBAL';
			}
			
			 $rfqid = inserttable ( "tbl_radio_freq", $radioFreqsarr );
			
			if ($rfqid) {
				$reqnum ++;
			}
			$reqnum1 ++;
			
			while ( $value1 = $_SGLOBAL ['db']->fetch_array ( $rp ) ) {
				$playBillarr = array ("radio_id" => $radioid, "title" => $value1 ['title'], "start_time" => $value1 ['broadcasttime'], "broadcaster" => $value1 ['broadcaster'], "create_time" => time (), "update_time" => time () );
				$pbid = inserttable ( "tbl_playbill", $playBillarr, 1 );
				if ($pbid) {
					$pbnum ++;
				}
				$pbnum1 ++;
			}
		
		}
	}
	
	echo "蜻蜓新数据补全完成！ ";
	
	echo "<br>";
	echo "<br>";
	
	echo "tbl_radio表应插入:$radionum.实插入:$radionum1";
	echo "<br>";
	echo "tbl_radio_freq表应插入:$reqnum.实插入:$reqnum1";
	echo "<br>";
	echo "tbl_playbill表应插入:$pbnum.实插入:$pbnum1";
	echo "<br>";
	echo "<br>";
	echo "<br>";
	echo "<br>";
	echo "<br>";



// 数据库连接
function dbconnect() {
	global $_SGLOBAL, $_SC;
	// include_once ('class_mysql.php');
	$_SGLOBAL ['db'] = new dbstuff ();
	$_SGLOBAL ['db']->charset = $_SC ['dbcharset'];
	$_SGLOBAL ['db']->connect ( $_SC ['dbhost'], $_SC ['dbuser'], $_SC ['dbpw'], $_SC ['dbname'], $_SC ['pconnect'] );
}

function dump($arr) {
	echo "<pre>";
	var_dump ( $arr );
	echo "<pre>";
}

function inserttable($tablename, $insertsqlarr, $returnid = 0, $replace = false, $silent = 0) {
	global $_SGLOBAL, $_SC;
	$tablename = $tablename;
	
	$insertkeysql = $insertvaluesql = $comma = '';
	foreach ( $insertsqlarr as $insert_key => $insert_value ) {
		$insertkeysql .= $comma . '`' . $insert_key . '`';
		$insertvaluesql .= $comma . '\'' . $insert_value . '\'';
		$comma = ', ';
	}
	$method = $replace ? 'REPLACE' : 'INSERT';
	$_SGLOBAL ['db']->query ( $method . ' INTO ' . $tablename . ' (' . $insertkeysql . ') VALUES (' . $insertvaluesql . ')', $silent ? 'SILENT' : '' );
	if ($returnid && ! $replace) {
		return $_SGLOBAL ['db']->insert_id ();
	}
}

function updatetable($tablename, $insertsqlarr, $wherearr) {
	global $_SGLOBAL, $_SC;
	$tablename = $tablename;
	
	$insertkeysql = $insertvaluesql = $comma = '';
	foreach ( $insertsqlarr as $insert_key => $insert_value ) {
		$insertkeysql .= $comma . '`' . $insert_key . '` = ' . '\'' . $insert_value . '\'';
		$comma = ', ';
	}
	$wheresql = " where ";
	foreach ( $wherearr as $w_key => $w_value ) {
		if($w_key == 0){
			$wheresql = $wheresql . $w_key . "=" . $w_value;
		}else{
			$wheresql = $wheresql . " and " . $w_key . "=" . $w_value;
		}
	}
	
	if($_SGLOBAL ['db']->query ( 'update ' . $tablename . ' set ' . $insertkeysql . $wheresql )){
		return 1;
	}else{
		return 0;
	}

}

function getRadioQt($isnull = 0) {
	global $_SGLOBAL, $_SC;
	$andstr = '';
	if ($isnull == 1) {
		$andstr = 'where tr.name="" or tr.name is null';
	} else {
		$andstr = "where  tr.name!='' and tqc.name!=''";
	}
	$sql = 'select 
					tqc.channelid,tqc.rid,tqc.name,tqc.description,tqc.type,tqc.frequency,tr.id,tr.name as rname,tr.channel,tr.description as rdescription,tr.play_url,source 
					from tbl_qt_channels tqc 
					left join tbl_radio tr 
					on tr.name=tqc.name ' . $andstr;#." limit 10";
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getRadioProgram($channelid = 0) {
	global $_SGLOBAL;
	
	$sql = 'select * from tbl_qt_channel_programs where channelid=' . $channelid;
	
	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );
	
	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}


function getRadioFreq($channelid = 0) {
	global $_SGLOBAL;

	$sql = 'select * from tbl_radio_freq where radio_id=' . $channelid;

	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );

	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

function getPlayBill($channelid = 0,$starttime) {
	global $_SGLOBAL;

	$sql = 'select * from tbl_playbill where radio_id=' . $channelid." and start_time='".$starttime."'" ;

	$query_offica1 = $_SGLOBAL ['db']->query ( $sql );

	return $query_offica1;
	// eturn $_SGLOBAL['db']->fetch_array($query_offica1);
}

class dbstuff {
	var $querynum = 0;
	var $link;
	var $charset;
	function connect($dbhost, $dbuser, $dbpw, $dbname = '', $pconnect = 0, $halt = TRUE) {
		if ($pconnect) {
			if (! $this->link = @mysql_pconnect ( $dbhost, $dbuser, $dbpw )) {
				$halt && $this->halt ( 'Can not connect to MySQL server' );
			}
		} else {
			if (! $this->link = @mysql_connect ( $dbhost, $dbuser, $dbpw, 1 )) {
				$halt && $this->halt ( 'Can not connect to MySQL server' );
			}
		}
		
		if ($this->version () > '4.1') {
			if ($this->charset) {
				@mysql_query ( "SET character_set_connection=$this->charset, character_set_results=$this->charset, character_set_client=binary", $this->link );
			}
			if ($this->version () > '5.0.1') {
				@mysql_query ( "SET sql_mode=''", $this->link );
			}
		}
		if ($dbname) {
			@mysql_select_db ( $dbname, $this->link );
		}
	}
	
	function select_db($dbname) {
		return mysql_select_db ( $dbname, $this->link );
	}
	
	function fetch_array($query, $result_type = MYSQL_ASSOC) {
		return mysql_fetch_array ( $query, $result_type );
	}
	
	function query($sql, $type = '') {
		if (D_BUG) {
			global $_SGLOBAL;
			$sqlstarttime = $sqlendttime = 0;
			$mtime = explode ( ' ', microtime () );
			$sqlstarttime = number_format ( ($mtime [1] + $mtime [0] - $_SGLOBAL ['supe_starttime']), 6 ) * 1000;
		}
		$func = $type == 'UNBUFFERED' && @function_exists ( 'mysql_unbuffered_query' ) ? 'mysql_unbuffered_query' : 'mysql_query';
		if (! ($query = $func ( $sql, $this->link )) && $type != 'SILENT') {
			$this->halt ( 'MySQL Query Error', $sql );
		}
		if (D_BUG) {
			$mtime = explode ( ' ', microtime () );
			$sqlendttime = number_format ( ($mtime [1] + $mtime [0] - $_SGLOBAL ['supe_starttime']), 6 ) * 1000;
			$sqltime = round ( ($sqlendttime - $sqlstarttime), 3 );
			
			$explain = array ();
			$info = mysql_info ();
			if ($query && preg_match ( "/^(select )/i", $sql )) {
				$explain = mysql_fetch_assoc ( mysql_query ( 'EXPLAIN ' . $sql, $this->link ) );
			}
			$_SGLOBAL ['debug_query'] [] = array ('sql' => $sql, 'time' => $sqltime, 'info' => $info, 'explain' => $explain );
		}
		$this->querynum ++;
		return $query;
	}
	
	function affected_rows() {
		return mysql_affected_rows ( $this->link );
	}
	
	function error() {
		return (($this->link) ? mysql_error ( $this->link ) : mysql_error ());
	}
	
	function errno() {
		return intval ( ($this->link) ? mysql_errno ( $this->link ) : mysql_errno () );
	}
	
	function result($query, $row) {
		$query = @mysql_result ( $query, $row );
		return $query;
	}
	
	function num_rows($query) {
		$query = mysql_num_rows ( $query );
		return $query;
	}
	
	function num_fields($query) {
		return mysql_num_fields ( $query );
	}
	
	function free_result($query) {
		return mysql_free_result ( $query );
	}
	
	function insert_id() {
		return ($id = mysql_insert_id ( $this->link )) >= 0 ? $id : $this->result ( $this->query ( "SELECT last_insert_id()" ), 0 );
	}
	
	function fetch_row($query) {
		$query = mysql_fetch_row ( $query );
		return $query;
	}
	
	function fetch_fields($query) {
		return mysql_fetch_field ( $query );
	}
	
	function version() {
		return mysql_get_server_info ( $this->link );
	}
	
	function close() {
		return mysql_close ( $this->link );
	}
	
	function halt($message = '', $sql = '') {
		$dberror = $this->error ();
		$dberrno = $this->errno ();
		$help_link = "http://faq.comsenz.com/?type=mysql&dberrno=" . rawurlencode ( $dberrno ) . "&dberror=" . rawurlencode ( $dberror );
		echo "<div style=\"position:absolute;font-size:11px;font-family:verdana,arial;background:#EBEBEB;padding:0.5em;\">
		<b>MySQL Error</b><br>
		<b>Message</b>: $message<br>
		<b>SQL</b>: $sql<br>
		<b>Error</b>: $dberror<br>
		<b>Errno.</b>: $dberrno<br>
		<a href=\"$help_link\" target=\"_blank\">Click here to seek help.</a>
		</div>";
		exit ();
	}
}

